Mapping Brooklyn Borough to visualize the areas of interest to find out the food desert

As per the USDA, an area can be classified as food desert if it meets the following two conditions:

1. Poverty Rate is greater than or equal to 20% or a household with income less than the federal poverty level of USD 17,050 for a family of four in 2000

2. At least 500 people or 33% of the population is located more than 1 mile (Urban) and 10 miles (Rural) from the nearest supermarket or grocery store

Insights found during Visualization

In Brooklyn, there are multiple census tracts having greater than or equal to 20% poverty rate which meets condition one but not a single tract meets condition 2

### Importing GIS Libraries
import geopandas as gpd
import plotly.graph_objects as go

### Importing data preprocessing & visualization libraries
import matplotlib.pyplot as plt
import pandas as pd
import numpy as np
import seaborn as sns

### Importing warning library
import warnings
warnings.filterwarnings("ignore")

Database 1: New York State Map

### Storing New York Map coordinates of 2019 as a pandas dataframe
ny_map = gpd.read_file(r'C:\Users\tuhin\OneDrive\Desktop\Omdena Brooklyn\cb_2019_36_tract_500k.shp', encoding='utf-8')

### Checking how the dataframe looks like
ny_map.head(2)

	STATEFP	COUNTYFP	TRACTCE	AFFGEOID	GEOID	NAME	LSAD	ALAND	AWATER	geometry
0	36	071	000502	1400000US36071000502	36071000502	5.02	CT	967431	969216	POLYGON ((-74.02226 41.49281, -74.02180 41.496...
1	36	103	135208	1400000US36103135208	36103135208	1352.08	CT	2287077	0	POLYGON ((-73.28263 40.83063, -73.28157 40.832...

### Plotting the map of New York state

fig, ax = plt.subplots(figsize=(20,20))
ny_map.plot(ax=ax)
plt.show()

### Checking if all New York Counties are present in the dataframe

print("The total number of counties present in New York is", ny_map['COUNTYFP'].nunique())

print("Showing all county codes")
ny_map["COUNTYFP"].unique()

The total number of counties present in New York is 62
Showing all county codes

array(['071', '103', '047', '055', '005', '029', '061', '119', '033',
       '001', '085', '069', '067', '081', '007', '107', '051', '059',
       '073', '015', '063', '053', '065', '083', '027', '117', '009',
       '057', '037', '093', '021', '087', '121', '111', '105', '043',
       '019', '109', '023', '011', '113', '091', '035', '013', '099',
       '075', '039', '089', '045', '101', '095', '003', '025', '049',
       '097', '115', '017', '031', '077', '079', '123', '041'],
      dtype=object)

## Select "Kings County" which is code - 047 (Brooklyn Borough)

kings_map = ny_map[ny_map["COUNTYFP"]=="047"]

## Checking the column headers of kings dataframe
kings_map.head(5)

	STATEFP	COUNTYFP	TRACTCE	AFFGEOID	GEOID	NAME	LSAD	ALAND	AWATER	geometry
2	36	047	057800	1400000US36047057800	36047057800	578	CT	172233	0	POLYGON ((-73.95398 40.60140, -73.95304 40.601...
3	36	047	058900	1400000US36047058900	36047058900	589	CT	424025	38353	POLYGON ((-73.94605 40.72926, -73.94419 40.729...
18	36	047	037000	1400000US36047037000	36047037000	370	CT	336242	0	POLYGON ((-73.96615 40.58667, -73.96564 40.588...
19	36	047	019900	1400000US36047019900	36047019900	199	CT	188914	0	POLYGON ((-73.96949 40.68629, -73.96749 40.686...
26	36	047	040000	1400000US36047040000	36047040000	400	CT	192000	0	POLYGON ((-73.98078 40.59845, -73.97986 40.598...

Database 2: Food Access Research Atlas 2019 - Kings County

### Reading the Food Access Research Atlas of 2019

kings_foodaccess = pd.read_csv('kings_data.csv')
kings_foodaccess.head(2)

	CensusTract	Track Code	State	County	Urban	Pop2010	OHU2010	GroupQuartersFlag	NUMGQTRS	PCTGQTRS	...	TractSeniors	TractWhite	TractBlack	TractAsian	TractNHOPI	TractAIAN	TractOMultir	TractHispanic	TractHUNV	TractSNAP
0	36047000100	100	New York	Kings County	1	4338	2227	0	424	9.77	...	843	3396	427	330	0	15	170	347	1417	126
1	36047000200	200	New York	Kings County	1	1470	360	0	39	2.65	...	70	530	103	49	0	16	772	1277	265	138

2 rows × 148 columns

### Imputing leading zeroes which usually gets removed when reading a csv file
kings_foodaccess['Track Code'] = kings_foodaccess['Track Code'].astype(str)
kings_foodaccess['Track Code'] = kings_foodaccess['Track Code'].str.zfill(6)

kings_foodaccess.head(2)

	CensusTract	Track Code	State	County	Urban	Pop2010	OHU2010	GroupQuartersFlag	NUMGQTRS	PCTGQTRS	...	TractSeniors	TractWhite	TractBlack	TractAsian	TractNHOPI	TractAIAN	TractOMultir	TractHispanic	TractHUNV	TractSNAP
0	36047000100	000100	New York	Kings County	1	4338	2227	0	424	9.77	...	843	3396	427	330	0	15	170	347	1417	126
1	36047000200	000200	New York	Kings County	1	1470	360	0	39	2.65	...	70	530	103	49	0	16	772	1277	265	138

2 rows × 148 columns

### TRACTCE will be the primary key to link all the database.
## Renaming Tract Code as "TRACTCE"

kings_foodaccess = kings_foodaccess.rename(columns={'Track Code': 'TRACTCE'})
kings_foodaccess.head(2)

	CensusTract	TRACTCE	State	County	Urban	Pop2010	OHU2010	GroupQuartersFlag	NUMGQTRS	PCTGQTRS	...	TractSeniors	TractWhite	TractBlack	TractAsian	TractNHOPI	TractAIAN	TractOMultir	TractHispanic	TractHUNV	TractSNAP
0	36047000100	000100	New York	Kings County	1	4338	2227	0	424	9.77	...	843	3396	427	330	0	15	170	347	1417	126
1	36047000200	000200	New York	Kings County	1	1470	360	0	39	2.65	...	70	530	103	49	0	16	772	1277	265	138

2 rows × 148 columns

### We need to map TRACTCE in kings database and TRACTCE in Food Atlas 2019 database to create a single database

first_compiled = kings_map.merge(kings_foodaccess, on='TRACTCE')

### Checking the total number of census tracks present in Kings County (Brooklyn Borough)

first_compiled['TRACTCE'].nunique()

754

### Visualizing the Brooklyn Borough with Poverty Rate as one of the key metric

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=first_compiled.geometry.__geo_interface__,
                                   locations=first_compiled.index,
                                   z=first_compiled['PovertyRate'],
                                   colorscale='OrRd',
                                   zmin=first_compiled['PovertyRate'].min(),
                                   zmax=first_compiled['PovertyRate'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover option Over Text
hover_text = []
for idx, row in first_compiled.iterrows():
    hover_text.append(f"Census Tract Area: {row['TRACTCE']}<br>PovertyRate: {row['PovertyRate']}")

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": first_compiled.geometry.centroid.y.mean(), 
                                 "lon": first_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='Poverty Rate')
fig.show()

Visualizing Low Income and Low Access Census Tracts which are 1/2 mile from nearest supermarket

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=first_compiled.geometry.__geo_interface__,
                                   locations=first_compiled.index,
                                   z=first_compiled['LILATracts_halfAnd10'],
                                   colorscale='OrRd',
                                   zmin=first_compiled['LILATracts_halfAnd10'].min(),
                                   zmax=first_compiled['LILATracts_halfAnd10'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in first_compiled.iterrows():
    hover_text.append(f"Census Tract Area: {row['TRACTCE']}<br>Low Income & Low Access - 1/2 mile: {row['PovertyRate']}")

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": first_compiled.geometry.centroid.y.mean(), "lon": first_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='LILATracts_halfAnd10')
fig.show()

Database 3: Grocery Store 2003 - 2017

This database contains the following

1. 445110: supermarkets and grocery stores, excluding convenience stores.
2. 4452: all specialty food stores. These include meat, fish, and seafood markets, produce markets, baked goods stores, and spice stores.
3. 452311: warehouse clubs and supercenters, which sell fresh food alongside canned and packaged foods and other types of merchandise (such as clothing and furniture).

2017 Grocery data was used as a proxy to 2019 Food Access Research Atlas data

grocery_data = pd.read_csv('Grocery store database.csv')
grocery_data.head(2)

	tract_fips10	year	population	aland10	count_445110	count_sales_445110	count_emp_445110	popden_445110	popden_sales_445110	popden_emp_445110	...	aden_emp_4452	count_452311	count_sales_452311	count_emp_452311	popden_452311	popden_sales_452311	popden_emp_452311	aden_452311	aden_sales_452311	aden_emp_452311
0	1001020100	2003	1858.883	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
1	1001020100	2004	1852.185	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0

2 rows × 31 columns

grocery_data['tract_fips10'] = grocery_data['tract_fips10'].astype(str)
grocery_data['tract_fips10'] = grocery_data['tract_fips10'].str.zfill(11)

grocery_data.head(2)

	tract_fips10	year	population	aland10	count_445110	count_sales_445110	count_emp_445110	popden_445110	popden_sales_445110	popden_emp_445110	...	aden_emp_4452	count_452311	count_sales_452311	count_emp_452311	popden_452311	popden_sales_452311	popden_emp_452311	aden_452311	aden_sales_452311	aden_emp_452311
0	01001020100	2003	1858.883	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
1	01001020100	2004	1852.185	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0

2 rows × 31 columns

# Extracting portions
grocery_data['State_Code'] = grocery_data['tract_fips10'].str[:2]
grocery_data['County_Code'] = grocery_data['tract_fips10'].str[2:5]
grocery_data['Census_Tract_Code'] = grocery_data['tract_fips10'].str[-6:]

grocery_data.head(2)

	tract_fips10	year	population	aland10	count_445110	count_sales_445110	count_emp_445110	popden_445110	popden_sales_445110	popden_emp_445110	...	count_emp_452311	popden_452311	popden_sales_452311	popden_emp_452311	aden_452311	aden_sales_452311	aden_emp_452311	State_Code	County_Code	Census_Tract_Code
0	01001020100	2003	1858.883	3.787641	0	0	0	0.0	0.0	0.0	...	0	0.0	0.0	0.0	0.0	0.0	0.0	01	001	020100
1	01001020100	2004	1852.185	3.787641	0	0	0	0.0	0.0	0.0	...	0	0.0	0.0	0.0	0.0	0.0	0.0	01	001	020100

2 rows × 34 columns

### Selecting New York State (State Code = 36)
grocery_ny = grocery_data[grocery_data["State_Code"]=="36"]

### Selecting Kings County (county code = 047)
grocery_kings = grocery_ny[grocery_ny['County_Code'] == '047']

grocery_kings

	tract_fips10	year	population	aland10	count_445110	count_sales_445110	count_emp_445110	popden_445110	popden_sales_445110	popden_emp_445110	...	count_emp_452311	popden_452311	popden_sales_452311	popden_emp_452311	aden_452311	aden_sales_452311	aden_emp_452311	State_Code	County_Code	Census_Tract_Code
620074	36047000100	2003	5254.272	0.08627	4	4	4	0.761285	0.761285	0.761285	...	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047	000100
620075	36047000100	2004	5164.090	0.08627	4	4	4	0.774580	0.774580	0.774580	...	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047	000100
620076	36047000100	2005	5073.908	0.08627	3	3	3	0.591260	0.591260	0.591260	...	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047	000100
620077	36047000100	2006	4983.727	0.08627	3	2	3	0.601959	0.401306	0.601959	...	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047	000100
620078	36047000100	2007	4893.545	0.08627	4	3	4	0.817403	0.613053	0.817403	...	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047	000100
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
630723	36047990100	2012	0.000	0.00000	0	0	0	NaN	NaN	NaN	...	0	NaN	NaN	NaN	NaN	NaN	NaN	36	047	990100
630724	36047990100	2013	0.000	0.00000	0	0	0	NaN	NaN	NaN	...	0	NaN	NaN	NaN	NaN	NaN	NaN	36	047	990100
630725	36047990100	2014	0.000	0.00000	0	0	0	NaN	NaN	NaN	...	0	NaN	NaN	NaN	NaN	NaN	NaN	36	047	990100
630726	36047990100	2015	0.000	0.00000	0	0	0	NaN	NaN	NaN	...	0	NaN	NaN	NaN	NaN	NaN	NaN	36	047	990100
630727	36047990100	2017	0.000	0.00000	0	0	0	NaN	NaN	NaN	...	0	NaN	NaN	NaN	NaN	NaN	NaN	36	047	990100

10654 rows × 34 columns

### TRACTCE will be the primary key to link all the database.
## Renaming Census Tract Code as "TRACTCE"

grocery_kings = grocery_kings.rename(columns={'Census_Tract_Code': 'TRACTCE'})

### Selecting only 2017 data

grocery_kings_17 = grocery_kings[grocery_kings['year'] == 2017]

grocery_kings_17['TRACTCE'].nunique()

761

### We need to map TRACTCE in first_compiled and TRACTCE in grocery database to create a single database

second_compiled = first_compiled.merge(grocery_kings_17, on='TRACTCE')

second_compiled.head(2)

	STATEFP	COUNTYFP	TRACTCE	AFFGEOID	GEOID	NAME	LSAD	ALAND	AWATER	geometry	...	count_sales_452311	count_emp_452311	popden_452311	popden_sales_452311	popden_emp_452311	aden_452311	aden_sales_452311	aden_emp_452311	State_Code	County_Code
0	36	047	057800	1400000US36047057800	36047057800	578	CT	172233	0	POLYGON ((-73.95398 40.60140, -73.95304 40.601...	...	0	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047
1	36	047	058900	1400000US36047058900	36047058900	589	CT	424025	38353	POLYGON ((-73.94605 40.72926, -73.94419 40.729...	...	0	0	0.0	0.0	0.0	0.0	0.0	0.0	36	047

2 rows × 190 columns

second_compiled.info()

<class 'geopandas.geodataframe.GeoDataFrame'>
Int64Index: 754 entries, 0 to 753
Columns: 190 entries, STATEFP to County_Code
dtypes: float64(133), geometry(1), int64(44), object(12)
memory usage: 1.1+ MB

Database 4: Socio-Economic Data

socio = pd.read_csv('socio_eco_data.csv')
socio.head(2)

	TRACT_FIPS20	ALAND20	TOTPOP20	POPDEN16_20	PHISPANIC16_20	PNHWHITE16_20	PNHBLACK16_20	PFBORN16_20	PLIMENG16_20	PED1_16_20	...	POWNOC16_20	AFFLUENCE16_20	DISADVANTAGE16_20	HISPAN_FORBORN_LIMENG16_20	MEDFAMINC16_20	MEDFAMINC_NHWHITE16_20	MEDFAMINC_BLACK16_20	MEDFAMINC_HISPANIC16_20	RATIO_MEDFAMINC_NHWTOB16_20	RATIO_MEDFAMINC_NHWTOHISP16_20
0	1001020100	3.793571	1941	511.6551209	0.017001545	0.774858296	0.121071614	0.002060793	0	0.142857149	...	0.731601715	0.256776839	0.171104908	0.006354113	70699	71324	75104		0.949669778
1	1001020200	1.282175	1757	1370.327881	0.017074559	0.364826411	0.583949924	0.00284576	0	0.105864435	...	0.684118688	0.224515289	0.208804905	0.006640106	50133	83750	47604		1.759305954

2 rows × 39 columns

socio['TRACT_FIPS20'] = socio['TRACT_FIPS20'].astype(str)
socio['TRACT_FIPS20'] = socio['TRACT_FIPS20'].str.zfill(11)

socio.head(2)

	TRACT_FIPS20	ALAND20	TOTPOP20	POPDEN16_20	PHISPANIC16_20	PNHWHITE16_20	PNHBLACK16_20	PFBORN16_20	PLIMENG16_20	PED1_16_20	...	POWNOC16_20	AFFLUENCE16_20	DISADVANTAGE16_20	HISPAN_FORBORN_LIMENG16_20	MEDFAMINC16_20	MEDFAMINC_NHWHITE16_20	MEDFAMINC_BLACK16_20	MEDFAMINC_HISPANIC16_20	RATIO_MEDFAMINC_NHWTOB16_20	RATIO_MEDFAMINC_NHWTOHISP16_20
0	01001020100	3.793571	1941	511.6551209	0.017001545	0.774858296	0.121071614	0.002060793	0	0.142857149	...	0.731601715	0.256776839	0.171104908	0.006354113	70699	71324	75104		0.949669778
1	01001020200	1.282175	1757	1370.327881	0.017074559	0.364826411	0.583949924	0.00284576	0	0.105864435	...	0.684118688	0.224515289	0.208804905	0.006640106	50133	83750	47604		1.759305954

2 rows × 39 columns

# Extracting portions
socio['State_Code'] = socio['TRACT_FIPS20'].str[:2]
socio['County_Code'] = socio['TRACT_FIPS20'].str[2:5]
socio['Census_Tract_Code'] = socio['TRACT_FIPS20'].str[-6:]

socio.head(2)

	TRACT_FIPS20	ALAND20	TOTPOP20	POPDEN16_20	PHISPANIC16_20	PNHWHITE16_20	PNHBLACK16_20	PFBORN16_20	PLIMENG16_20	PED1_16_20	...	HISPAN_FORBORN_LIMENG16_20	MEDFAMINC16_20	MEDFAMINC_NHWHITE16_20	MEDFAMINC_BLACK16_20	MEDFAMINC_HISPANIC16_20	RATIO_MEDFAMINC_NHWTOB16_20	RATIO_MEDFAMINC_NHWTOHISP16_20	State_Code	County_Code	Census_Tract_Code
0	01001020100	3.793571	1941	511.6551209	0.017001545	0.774858296	0.121071614	0.002060793	0	0.142857149	...	0.006354113	70699	71324	75104		0.949669778		01	001	020100
1	01001020200	1.282175	1757	1370.327881	0.017074559	0.364826411	0.583949924	0.00284576	0	0.105864435	...	0.006640106	50133	83750	47604		1.759305954		01	001	020200

2 rows × 42 columns

### Selecting New York State (State Code = 36)
socio_ny = socio[socio["State_Code"]=="36"]

### Selecting Kings County (county code = 047)
socio_kings = socio_ny[socio_ny['County_Code'] == '047']

socio_kings.head(2)

	TRACT_FIPS20	ALAND20	TOTPOP20	POPDEN16_20	PHISPANIC16_20	PNHWHITE16_20	PNHBLACK16_20	PFBORN16_20	PLIMENG16_20	PED1_16_20	...	HISPAN_FORBORN_LIMENG16_20	MEDFAMINC16_20	MEDFAMINC_NHWHITE16_20	MEDFAMINC_BLACK16_20	MEDFAMINC_HISPANIC16_20	RATIO_MEDFAMINC_NHWTOB16_20	RATIO_MEDFAMINC_NHWTOHISP16_20	State_Code	County_Code	Census_Tract_Code
50941	36047000100	0.080239	4539	56568.24609	0.069839172	0.645296335	0.146948665	0.124476761	0.022790698	0.003593145	...	0.072368875	195292	228307					36	047	000100
50942	36047000200	0.106923	992	9277.713867	0.73286289	0.164314523	0.021169355	0.353830636	0.273565561	0.37416777	...	0.453419685							36	047	000200

2 rows × 42 columns

socio_kings = socio_kings.rename(columns={'Census_Tract_Code': 'TRACTCE'})

socio_kings.head(1)

	TRACT_FIPS20	ALAND20	TOTPOP20	POPDEN16_20	PHISPANIC16_20	PNHWHITE16_20	PNHBLACK16_20	PFBORN16_20	PLIMENG16_20	PED1_16_20	...	HISPAN_FORBORN_LIMENG16_20	MEDFAMINC16_20	MEDFAMINC_NHWHITE16_20	MEDFAMINC_BLACK16_20	MEDFAMINC_HISPANIC16_20	RATIO_MEDFAMINC_NHWTOB16_20	RATIO_MEDFAMINC_NHWTOHISP16_20	State_Code	County_Code	TRACTCE
50941	36047000100	0.080239	4539	56568.24609	0.069839172	0.645296335	0.146948665	0.124476761	0.022790698	0.003593145	...	0.072368875	195292	228307					36	047	000100

1 rows × 42 columns

socio_kings['TRACTCE'].nunique()

805

third_compiled = pd.merge(second_compiled, socio_kings, on='TRACTCE', how='left')

third_compiled.info()

<class 'geopandas.geodataframe.GeoDataFrame'>
Int64Index: 754 entries, 0 to 753
Columns: 231 entries, STATEFP to County_Code_y
dtypes: float64(135), geometry(1), int64(44), object(51)
memory usage: 1.3+ MB

Database 5: Eating & Drinking Data

This database contains the following

1. 7225: all restaurants and eating places.
2. 722511: full-service restaurants where patrons order and are served while seated.
3. 722513: limited service restaurants, e.g. fast-food restaurants.
4. 722515: snack and nonalcoholic beverage bars, such as coffee shops, doughnut or bagel shops, and ice cream parlors.
5. 722410: drinking places (alcoholic beverages), such as bars, taverns, and cocktail lounges.

2017 Eating & Drinking Data is used as a proxy to 2019 Food Access Research Atlas data

eating = pd.read_csv('eating.csv')
eating.head(2)

	tract_fips10	year	population	aland10	count_7225	count_sales_7225	count_emp_7225	popden_7225	popden_sales_7225	popden_emp_7225	...	aden_emp_722515	count_722410	count_sales_722410	count_emp_722410	popden_722410	popden_sales_722410	popden_emp_722410	aden_722410	aden_sales_722410	aden_emp_722410
0	1001020100	2003	1858.883	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
1	1001020100	2004	1852.185	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0

2 rows × 49 columns

eating['tract_fips10'] = eating['tract_fips10'].astype(str)
eating['tract_fips10'] = eating['tract_fips10'].str.zfill(11)

eating.head(2)

	tract_fips10	year	population	aland10	count_7225	count_sales_7225	count_emp_7225	popden_7225	popden_sales_7225	popden_emp_7225	...	aden_emp_722515	count_722410	count_sales_722410	count_emp_722410	popden_722410	popden_sales_722410	popden_emp_722410	aden_722410	aden_sales_722410	aden_emp_722410
0	01001020100	2003	1858.883	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0
1	01001020100	2004	1852.185	3.787641	0	0	0	0.0	0.0	0.0	...	0.0	0	0	0	0.0	0.0	0.0	0.0	0.0	0.0

2 rows × 49 columns

# Extracting portions
eating['State_Code'] = eating['tract_fips10'].str[:2]
eating['County_Code'] = eating['tract_fips10'].str[2:5]
eating['TRACTCE'] = eating['tract_fips10'].str[-6:]

### Selecting New York State (State Code = 36)
eating_ny = eating[eating["State_Code"]=="36"]

### Selecting Kings County (county code = 047)
eating_kings = eating_ny[eating_ny['County_Code'] == '047']

eating_kings_17 = eating_kings[eating_kings['year'] == 2017]

fourth_compiled = pd.merge(third_compiled, eating_kings_17, on='TRACTCE', how='left')

fourth_compiled.info()

<class 'geopandas.geodataframe.GeoDataFrame'>
Int64Index: 754 entries, 0 to 753
Columns: 282 entries, STATEFP to County_Code
dtypes: float64(167), geometry(1), int64(60), object(54)
memory usage: 1.6+ MB

Visualizing Poverty Rates with Median Family Income, Restaurants, Bars and Grocery stores

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
                                   locations=fourth_compiled.index,
                                   z=fourth_compiled['PovertyRate'],
                                   colorscale='OrRd',
                                   zmin=fourth_compiled['PovertyRate'].min(),
                                   zmax=fourth_compiled['PovertyRate'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
    hover_text.append(
        f"Census Tract Area: {row['TRACTCE']}<br>Population (2010): {row['Pop2010']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>Supermarkets: {row['count_445110']}<br>Speciality Stores: {row['count_4452']}<br>Warehouse Clubs/Supercentres: {row['count_452311']}<br>Fast Food Restaurants: {row['count_722513']}<br>Full Service Restaurants: {row['count_722511']}<br>Coffee Shops: {row['count_722515']}<br>Bars: {row['count_722410']}")
    

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(), 
                                 "lon": fourth_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='PovertyRate')

# Adding markers where LILATracts_halfAnd10 = 1
lila_tracts = fourth_compiled[fourth_compiled['LILATracts_halfAnd10'] == 1]
fig.add_trace(go.Scattermapbox(
    lat=lila_tracts.geometry.centroid.y,
    lon=lila_tracts.geometry.centroid.x,
    mode='markers',
    marker=dict(size=10, color='blue', opacity=0.7),
    hoverinfo='skip'  
))

fig.show()

Visualizing Fast food restaurants (in blue) with median family income, poverty rates, higher education, SNAP Benefits(%), grocery stores

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
                                   locations=fourth_compiled.index,
                                   z=fourth_compiled['count_722513'],
                                   colorscale='blues',
                                   zmin=fourth_compiled['count_722513'].min(),
                                   zmax=fourth_compiled['count_722513'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
    hover_text.append(
        f"Census Tract Area: {row['TRACTCE']}<br>Population (2010): {row['Pop2010']}<br>Fast Food Rests: {row['count_722513']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']} <br>UG & PG Edu: {row['PPROF16_20']}<br>TractSNAP (%): {round((row['TractSNAP']/row['OHU2010'])*100, 2)}<br>Supermarkets: {row['count_445110']}<br>Speciality Stores: {row['count_4452']}<br>Warehouse Clubs/Supercentres: {row['count_452311']}")
    

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(), 
                                 "lon": fourth_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='PovertyRate')

# Adding markers where LILATracts_halfAnd10 = 1
lila_tracts = fourth_compiled[fourth_compiled['LILATracts_halfAnd10'] == 1]
fig.add_trace(go.Scattermapbox(
    lat=lila_tracts.geometry.centroid.y,
    lon=lila_tracts.geometry.centroid.x,
    mode='markers',
    marker=dict(size=10, color='blue', opacity=0.7),
    hoverinfo='skip'  
))

fig.show()

Visualing Poverty Rates with Demographics and Socio-Economic Factors in Brooklyn - PART 1

Demographic factors considered

1. White Population (%)
2. Black Population (%)
3. Asian Population (%)
4. Native Alaskan/Native Population (%)
5. Hispanic Population (%)
6. Other/Mixed Population (%)

Socio-Economic factors considered:

1. Median Family Income
2. Proportion of civilian employed with professional studies
3. Proportion of households with public assisted income/food stamps

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
                                   locations=fourth_compiled.index,
                                   z=fourth_compiled['PovertyRate'],
                                   colorscale='OrRd',
                                   zmin=fourth_compiled['PovertyRate'].min(),
                                   zmax=fourth_compiled['PovertyRate'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
    hover_text.append(
        f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Employed with Professional Studies (Prop): {row['PPROF16_20']}<br>Households w public assist income/food stamps (%): {row['PPUBAS16_20']}")
    

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(), 
                                 "lon": fourth_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='PovertyRate')

fig.show()

Visualing Poverty Rates with Demographics and Socio-Economic Factors in Brooklyn - PART 2

Demographic factors considered

1. White Population (%)
2. Black Population (%)
3. Asian Population (%)
4. Native Alaskan/Native Population (%)
5. Hispanic Population (%)
6. Other/Mixed Population (%)

Socio-Economic factors considered:

1. Median Family Income
2. Proportion of female-headed families with kids
3. Proportion of Single-parent families with kids

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
                                   locations=fourth_compiled.index,
                                   z=fourth_compiled['PovertyRate'],
                                   colorscale='OrRd',
                                   zmin=fourth_compiled['PovertyRate'].min(),
                                   zmax=fourth_compiled['PovertyRate'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
    hover_text.append(
        f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Female-headed families with kids (Prop): {row['PFHFAM16_20']}<br>Single-parent families with kids (Prop): {row['PSNGPNT16_20']}")
    

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(), 
                                 "lon": fourth_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='PovertyRate')

fig.show()

Visualing Poverty Rates with Demographics and Socio-Economic Factors in Brooklyn - PART 3

Demographics

1. White Population (%)
2. Black Population (%)
3. Asian Population (%)
4. Native Alaskan/Native Population (%)
5. Hispanic Population (%)
6. Other/Mixed Population (%)

Socio-Economic factors considered:

The socio-economic database has two important metrics to measure the level of socio-economic equity

1. Affluence_16_20
2. Disadvantage_16_20

The Affluence factor is the average of three variables given below

1. Proportions of people with Bachelor's degree or higher
2. Propotions of families with income more than USD 125K
3. Propotions of civilians employed in business, management, science and arts occupation

The Disadvantages factor is the average of three variables given below

1. Proportion of families with income less than USD 40k
2. Proportion people w/ income in the past 12 months below poverty level
3. Proportion of households with public assistance income or foodstamps

fig = go.Figure()

# Adding choropleth map
fig.add_trace(go.Choroplethmapbox(geojson=fourth_compiled.geometry.__geo_interface__,
                                   locations=fourth_compiled.index,
                                   z=fourth_compiled['PovertyRate'],
                                   colorscale='OrRd',
                                   zmin=fourth_compiled['PovertyRate'].min(),
                                   zmax=fourth_compiled['PovertyRate'].max(),
                                   marker_opacity=0.5,
                                   marker_line_width=0))
# Adding Hover Over Text
hover_text = []
for idx, row in fourth_compiled.iterrows():
    hover_text.append(
        f"Census Tract Area: {row['TRACTCE']}<br>Median Family Income: {row['MedianFamilyIncome']}<br>Poverty Rates: {row['PovertyRate']}<br>White pop (%): {round((row['TractWhite']/row['Pop2010'])*100, 2)}<br>Black Pop (%): {round((row['TractBlack']/row['Pop2010'])*100, 2)}<br>Asian Pop (%): {round((row['TractAsian']/row['Pop2010'])*100, 2)}<br>Native Alaskan/Indian Pop (%): {round((row['TractAIAN']/row['Pop2010'])*100, 2)}<br>Hispanic Pop (%): {round((row['TractHispanic']/row['Pop2010'])*100, 2)}<br>Other/Mixed Pop (%): {round((row['TractOMultir']/row['Pop2010'])*100, 2)} <br>Disadvantage: {row['DISADVANTAGE16_20']}<br>Affluence: {row['AFFLUENCE16_20']}")
    

fig.update_traces(hoverinfo='text', text=hover_text)

fig.update_layout(mapbox_style="carto-positron",
                  mapbox_zoom=8,
                  mapbox_center={"lat": fourth_compiled.geometry.centroid.y.mean(), 
                                 "lon": fourth_compiled.geometry.centroid.x.mean()},
                  margin={"r":0,"t":0,"l":0,"b":0},
                  hovermode='closest',
                  title='PovertyRate')

fig.show()